package DbUtil;

import com.alibaba.druid.pool.DruidDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ResourceBundle;


/**
 * 定义工具类
 */
public class DbUtils {
    //1.定义变量
    protected static Connection conn = null;
    protected static PreparedStatement pps = null;
    protected ResultSet resultSet = null;
    protected int k = 0;//受影响的行数
    private static String url;
    private static String userName;
    private static String password;
    private static String dirverName;
    //c3p0
    private ComboPooledDataSource db=new ComboPooledDataSource();
    //德鲁伊
    private static DruidDataSource ds = new DruidDataSource();

    //2.加载驱动类
    static {

        //德鲁伊
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        dirverName = bundle.getString("driver");
        url = bundle.getString("url");
        userName = bundle.getString("user");
        password = bundle.getString("password");

        //设置驱动类全称
        ds.setDriverClassName(dirverName);
        //设置连接的数据库
        ds.setUrl(url);
        //设置用户名
        ds.setUsername(userName);
        //设置密码
        ds.setPassword(password);
        ds.setInitialSize(8);
    }

    //3.建立连接方法
    protected Connection getConn() {
        try {
            conn = ds.getConnection();
            //conn = db.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //4.建立通道
    protected PreparedStatement getPps(String sql) {
        try {
            getConn();//insert into users values(?,?,?,?,)
            pps = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pps;
    }

    //5.给占位符赋值 list中保存的是给占位符所赋的值
    private void setParams(List list) {
        try {
            if (list != null && list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    pps.setObject(i + 1, list.get(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //6.增删改调取的方法
    protected int update(String sql, List params) {
        try {
            getPps(sql);
            setParams(params);
            k = pps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return k;
    }

    //7.查询的时候调取一个方法
    protected ResultSet query(String sql, List list) {
        try {
            getPps(sql);
            setParams(list);
            resultSet = pps.executeQuery();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //8.关闭资源
    protected void closeAll() {
        try {
            if (conn != null) {
                conn.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
}
